﻿Option Explicit On
'Option Strict On

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
'Imports CrystalDecisions.CrystalReports.Engine
'Imports CrystalDecisions.Shared

Public Class DBConnect
    Dim SqlConnectString As String = "server=dwarf.cis.temple.edu;Database=FA11_4396_all; User ID=GameAdmin;Password=projectbiome"
    'Dim ConnectString As String = "provider=SQLOLEDB.1;server=dwarf.cis.temple.edu;Database=fa08_c434201nn;User fa08_c201n434n;Password=YourPassword"
    'Dim ConnectString As String = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:`\users1\dl\Inventory.mdb"
    'Dim myConnection As New OleDbConnection(ConnectString)
    Dim myConnectionSql As New SqlConnection(SqlConnectString)
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader
    Dim ds As DataSet
    'Dim crConnection As New ConnectionInfo
    Public Sub New()
        myConnectionSql.Open()
    End Sub
    Public Function getDataSet(ByVal SqlSelect As String) As DataSet
        'InParam is a Select statement. Return is the Dataset 
        'Note: The Dataset is also stored as a class variable for use 
        'in the getField function
        'Dim myDataAdapter As New OleDbDataAdapter(SqlSelect, myConnection)
        Dim myDataAdapter As New SqlDataAdapter(SqlSelect, myConnectionSql)
        Dim myDataSet As New DataSet
        myDataAdapter.Fill(myDataSet)
        ds = myDataSet
        Return myDataSet
    End Function
    Public Function getDataSet(ByVal SqlSelect As String, ByRef theCount As Integer) As DataSet
        'InParam is a Select statement, OutParam is the number of rows 
        'in the returned dataset, Return is a Dataset
        'Dim myDataAdapter As New OleDbDataAdapter(SqlSelect, myConnection)
        Dim myDataAdapter As New SqlDataAdapter(SqlSelect, myConnectionSql)
        Dim myDataSet As New DataSet
        myDataAdapter.Fill(myDataSet)
        ds = myDataSet
        theCount = ds.Tables(0).Rows.Count()
        Return myDataSet
    End Function
    Public Function getDataSet(ByVal SqlSelect As String, ByRef theCount As Integer, ByRef theErrNum As Integer, ByRef theErrMessage As String) As DataSet
        'InParam is a Select statement, OutParams are (1)the number of rows 
        'in the returned dataset, (2)the error number.  If no exception occurs it is zero, 
        'else it is the error number, (3) the error message if an exception occurs.  
        'Returns a Dataset
        Try
            'Dim myDataAdapter As New OleDbDataAdapter(SqlSelect, myConnection)
            Dim myDataAdapter As New SqlDataAdapter(SqlSelect, myConnectionSql)
            Dim myDataSet As New DataSet
            myDataAdapter.Fill(myDataSet)
            ds = myDataSet
            theCount = ds.Tables(0).Rows.Count()
            theErrNum = 0
            Return myDataSet
        Catch e As Exception
            theErrNum = Err.Number
            theErrMessage = e.Message
        End Try
    End Function

    Public Function getDataReader(ByVal SqlSelect As String) As SqlDataReader
        'InParam is a Select statement. Returns DataReader
        objCmd = New SqlCommand(SqlSelect, myConnectionSql)
        Return objCmd.ExecuteReader()
    End Function
    'OLEDB version
    'Public Function getDataReader(ByVal SqlSelect As String) As OleDbDataReader
    '    'InParam is a Select statement. Returns DataReader
    '    objCmd = New OleDbCommand(SqlSelect, myConnection)
    '    Return objCmd.ExecuteReader()
    'End Function
    Public Function doUpdate(ByVal SqlManipulate As String) As Integer
        'InParam is a SQL manipulate statement (Insert, Update, Delete). 
        'Returns the number of rows affected by the update.
        'If an Exception occurs, -1 is returned
        objCmd = New SqlCommand(SqlManipulate, myConnectionSql)
        Try
            Return objCmd.ExecuteNonQuery()
        Catch e As Exception
            Return -1
        End Try
    End Function

    Public Function doUpdate2(ByVal SqlManipulate As String) As Integer
        'InParam is a SQL manipulate statement (Insert, Update, Delete). 
        'Returns the number of rows affected by the update.
        'If an Exception occurs, -1 is returned
        Dim newSQL As Integer
        objCmd = New SqlCommand(SqlManipulate, myConnectionSql)
        Try
            objCmd.ExecuteNonQuery()
            objCmd.CommandText = "Select @@Identity"
            newSQL = objCmd.ExecuteScalar()
            Return newSQL
        Catch e As Exception
            Return -1
        End Try
    End Function

    Public Function getDataSetUsingCmdObj(ByVal aCmdObj As SqlCommand) As DataSet
        'InParam is a Command object containing a SQL Select statement. 
        'Returns a DataSet.
        'This function is used for passing parameters to a Stored Procedure
        aCmdObj.Connection = myConnectionSql
        Dim myDataAdapter As New SqlDataAdapter(aCmdObj)
        Dim myDataSet As New DataSet
        myDataAdapter.Fill(myDataSet)
        ds = myDataSet
        Return myDataSet
    End Function
    Public Function doUpdateUsingCmdObj(ByVal aCmdObj As SqlCommand) As Integer
        'InParam is a Command object containing a SQL manipulate 
        'statement (Insert, Update, Delete). 
        'Returns the number of rows affected by the update.
        'If an Exception occurs, -1 is returned
        'This function is used for passing parameters to a Stored Procedure
        Try
            aCmdObj.Connection = myConnectionSql
            Return aCmdObj.ExecuteNonQuery()
        Catch e As Exception
            Return -1
        End Try
    End Function
    Public Function getRow(ByVal ds As DataSet, ByVal row As Integer) As DataRow
        'InParams are (1)a DataSet and (2) the zero based row of the 
        'table in the DataSet to be returned.  Returns a row.
        Dim objTable As DataTable = ds.Tables(0)
        Return objTable.Rows(row)
    End Function
    Public Function getField(ByVal FieldName As String, ByVal row As Integer) As Object
        'InParams are (1)a Field (Column) name as a string and
        '(2)the zero based row of the table in the DataSet 
        'from which the field is to be extracted. Returns the value 
        'in the field as a variant type.
        'This function assumes that one of the getDataSet functions
        'had been called, thus producing a ds at the class level.
        Dim objRow As DataRow
        Dim objTable As DataTable = ds.Tables(0)
        objRow = objTable.Rows(row)
        Return objRow(FieldName)
    End Function
    Public Function getRows(ByVal Condition As String) As Array
        Dim objRows() As DataRow
        Dim objTable As DataTable = ds.Tables(0)
        objRows = objTable.Select(Condition)
        Return objRows
    End Function
    Public Function CommitDataSet(ByVal ds As DataSet)
        'InParam is a DataSet. This function is used to Commit
        'the Dataset to the Data Source when updating a disconnected ds. 
        Dim myDataAdapter As New SqlDataAdapter
        myDataAdapter.Update(ds)
    End Function
    Public Function ExecScalarFunction(ByVal acmdObj As SqlCommand) As VariantType
        'InParam is a Command object containing a Select statement 
        'that returns a single scalar value. Returns the single scalar value.  
        'Returns  scalar value as a Variant Type.
        acmdObj.Connection = myConnectionSql
        Return CType(acmdObj.ExecuteScalar(), VariantType)
    End Function
    'OLEDB Version
    'Public Function getConnection() As OleDbConnection
    '    Return myConnection
    'End Function
    Public Function getConnection() As SqlConnection
        'NOTE: .NET has implemented its Stored User Defined Functions only 
        'With the Managed Provider for SQL Server, 
        'not the OLEDB provider. 
        Return myConnectionSql
    End Function
    Public Sub closeConSql()
        myConnectionSql.close()
    End Sub
    'Public Function getCrConnection() As ConnectionInfo
    '    With crConnection
    '        .ServerName = "dwarf.cis.temple.edu"
    '        .DatabaseName = "fa08_c434201nn"
    '        .UserID = "fa08_c434201nn"
    '        .Password = "YourPassword"
    '    End With
    '    Return crConnection
    'End Function
    Public Function resetConnection()
        myConnectionSql.Close()
        myConnectionSql.Open()
    End Function

    Function getDataReader() As SqlDataReader
        Throw New NotImplementedException
    End Function

End Class







